﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LumenWorks.Framework.IO.Csv;
using System.IO; 
using LumenWorks.Framework.Tests.Unit.IO.Csv;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Adex; 
using System.Transactions;
using System.Drawing;
using MediaMonitor.Common;
using System.Globalization;
using System.Resources;
using System.Threading;
using System.Reflection;
using System.Configuration;
using System.Net.Mail;

namespace MediaMonitor.Admin
{
    public partial class RF_Upload_NewsAlert : Adex.BasePage
    {
        private static string AppName = ConfigurationManager.AppSettings["AppName"].ToString();
        private static string AdminEmail = ConfigurationManager.AppSettings["AdminEmail"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }
        protected void btnUpload_Click(Object sender, EventArgs e)
        {
            string errMsg1, errMsg2, errMsg3, errMsg4, errMsg5, errMsg6, errMsg7;
            errMsg1 = errMsg2 = errMsg3 = errMsg4 = errMsg5 = errMsg6 = errMsg7 = "";
            if (flRF1.PostedFile.FileName.Length > 0)
            {
                lblInfo1.ForeColor = UploadRF(flRF1, out errMsg1) == true ? Color.Blue : Color.Red;
                lblInfo1.Text = errMsg1;
            }
            else
                lblInfo1.Text = "No file selected to upload";
            if (flRF2.PostedFile.FileName.Length > 0)
            {
                lblInfo2.ForeColor = UploadRF(flRF2, out errMsg2) == true ? Color.Blue : Color.Red;
                lblInfo2.Text = errMsg2;
            }
            else
                lblInfo2.Text = "No file selected to upload";
            if (flRF3.PostedFile.FileName.Length > 0)
            {
                lblInfo3.ForeColor = UploadRF(flRF3, out errMsg3) == true ? Color.Blue : Color.Red;
                lblInfo3.Text = errMsg3;
            }
            else
                lblInfo3.Text = "No file selected to upload";
            if (flRF4.PostedFile.FileName.Length > 0)
            {
                lblInfo4.ForeColor = UploadRF(flRF4, out errMsg4) == true ? Color.Blue : Color.Red;
                lblInfo4.Text = errMsg4;
            }
            else
                lblInfo4.Text = "No file selected to upload";
            if (flRF5.PostedFile.FileName.Length > 0)
            {
                lblInfo5.ForeColor = UploadRF(flRF5, out errMsg5) == true ? Color.Blue : Color.Red;
                lblInfo5.Text = errMsg5;
            }
            else
                lblInfo5.Text = "No file selected to upload";
            if (flRF6.PostedFile.FileName.Length > 0)
            {
                lblInfo6.ForeColor = UploadRF(flRF6, out errMsg6) == true ? Color.Blue : Color.Red;
                lblInfo6.Text = errMsg6;
            }
            else
                lblInfo6.Text = "No file selected to upload";
            if (flRF7.PostedFile.FileName.Length > 0)
            {
                lblInfo7.ForeColor = UploadRF(flRF7, out errMsg7) == true ? Color.Blue : Color.Red;
                lblInfo7.Text = errMsg7;
            }
            else
                lblInfo7.Text = "No file selected to upload";
            pnlInfo1.Visible = pnlInfo2.Visible = pnlInfo3.Visible = pnlInfo4.Visible = pnlInfo5.Visible = pnlInfo6.Visible = pnlInfo7.Visible = true;
            pnlUpload.Visible = false;
        }
       
        private bool UploadRF(FileUpload flRF, out string errMsg)
        {
            byte countryID = AppUser.GetCountry();

            StreamReader readFile = new StreamReader(flRF.PostedFile.InputStream, System.Text.Encoding.GetEncoding(1252),false, 4096);
            CsvReader csv = new CsvReader(new StringReader(readFile.ReadToEnd()), false, ',', '"', '\\', '☺', ValueTrimmingOptions.All); // for comments char = 122
            DataTable dt = GetRFDataTable();
            if (csv.FieldCount != dt.Columns.Count - 1)
            {
                errMsg = "<h2>Upload Failed!!!</h2>Not a valid RF file. Field length is different";
                return false;
            }
            int currentRow = 0;
            int currentCol = 0;
            try
            {
                //fill data from RF file
                while (csv.ReadNextRecord())
                {
                    currentRow += 1;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < dt.Columns.Count - 1; i++)
                    {
                        currentCol = i + 1;
                        switch (dt.Columns[i + 1].DataType.Name)
                        {
                            case "DateTime":
                                dr[i + 1] = Utility.IsNullDate(Utility.GetDateNewsAlertFromNumber(csv[i].ToString()));
                                break;
                            case "Int32":
                                dr[i + 1] = Utility.IsNullInt(csv[i]);
                                break;
                            case "String":
                                dr[i + 1] = Utility.IsNullStr(csv[i]);
                                break;
                            case "Boolean":
                                dr[i + 1] = Utility.IsNullBool(csv[i]);
                                break;
                            case "Decimal":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                            case "Double":
                                dr[i + 1] = Utility.IsNullDec(csv[i]);
                                break;
                        }//end swtich
                    }
                    dt.Rows.Add(dr);
                }
            }
            catch (Exception ex1)
            {
                errMsg = "<h2>Upload Failed!!!</h2>Failed to shift data from CSV to DB. CSV Row: " + currentRow + ", Col: " + currentCol + "." + ex1.Message;
                return false;
            }
            TransactionOptions options = new TransactionOptions();
            options.Timeout = new TimeSpan(0, 2, 0);
            options.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
            DateTime rfDate = DateTime.Now.AddYears(50);
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress, options))
            {
                using (SubSonic.SharedDbConnectionScope conn = new SubSonic.SharedDbConnectionScope(System.Configuration.ConfigurationManager.ConnectionStrings["DBAdex"].ToString()))
                {
                    //transaction activities.
                    try
                    {
                        //string Mailbody = " <table width='100%'>";
                        foreach (DataRow dr in dt.Rows)
                        {
                            rfDate = Utility.IsNullDate(dr["RFDate"], DateTime.MinValue);
                            int mediaCode = Utility.IsNullInt(dr["MediaCode"]);
                            int mediaTypeCode = MediaMonitor.Common.App.GetMediaTypeCode(mediaCode);
                            string mediaType = "";
                            switch (mediaTypeCode)
                            {
                                case 1:
                                case 3:
                                case 5:
                                    {
                                        mediaType = "TVCR";
                                        break;
                                    }
                                case 6:
                                    {
                                        mediaType = "PR";
                                        break;
                                    }
                                case 7:
                                    {
                                        mediaType = "OD";
                                        break;
                                    }
                                case 8:
                                    {
                                        mediaType = "NS";
                                        break;
                                    }
                                case 9:
                                    {
                                        mediaType = "IN";
                                        break;
                                    }
                            }
                            #region  NewsAlert Log 
                            TblNewsAlert prLog = new TblNewsAlert();
                            prLog.MediaCode = mediaCode;                       //1
                            prLog.RFDate = rfDate;                             //2
                            prLog.MediaDescription = Utility.IsNullStr(dr["MediaDescription"]);//3
                            prLog.SectionCode = Convert.ToInt32(dr["SectionCode"]);    //4
                            prLog.SectionDescription = Utility.IsNullStr(dr["SectionDescription"]);//5
                            prLog.PageorHour = dr["PageorHour"].ToString();      //6
                            prLog.Duration = Utility.IsNullStr(dr["Duration"]);//7
                            prLog.Advertiser = Utility.IsNullStr(dr["Advertiser"]);//8
                            prLog.Title = Utility.IsNullStr(dr["Title"]);        //9
                            prLog.Calification = Utility.IsNullStr(dr["Calification"]);//10
                            prLog.Important = Utility.IsNullStr(dr["Important"]);    //11
                            prLog.NewsSummary = Utility.IsNullStr(dr["NewsSummary"]); //12
                            prLog.OthersBrandsDnvolved = Utility.IsNullStr(dr["OthersBrandsDnvolved"]);             //13
                            prLog.OthersPeoplesInvolved = Utility.ToStr(dr["OthersPeoplesInvolved"]);         //14
                            prLog.Link = Utility.IsNullStr(dr["Link"]);                       //15
                            prLog.ImageAudioVideoName = Utility.IsNullStr(dr["ImageAudioVideoName"]);//16
                            prLog.News = Utility.IsNullStr(dr["News"]);  //17
                            prLog.Company = Utility.IsNullStr(dr["Company"]);//18
                            //otherPeopleName += Utility.ToStr(dr["OthersPeoplesInvolved"]) + ",";
                            prLog.Save();
                            int NewsID = Convert.ToInt32(SPs.GetLastInsertValue().ExecuteScalar());
                            SPs.AddupdateOtherPeopleName(Utility.ToStr(dr["OthersPeoplesInvolved"].ToString()), ",", 0, NewsID).Execute();
                            //string Pagina = SPs.GetmediatypecodeForPagina(mediaCode.ToString()).ExecuteScalar().ToString();
                            //if (Pagina == "6")
                            //    Pagina = "Pagina";
                            //else
                            //    Pagina = " Hora";
                            //Mailbody += "<tr><td> <b style='color: #0066FF;'>" + prLog.Title + "</b><br/><br/>";
                            //Mailbody += "<b style='color: #cd9035'>Fecha:</b><b style='color: #253e5c; font-weight: normal;'>" + rfDate + "</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>" + Pagina + "</b><b style='color: #253e5c; font-weight: normal;'>" + dr["PageorHour"].ToString() + "</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>Duracion:</b><b style='color: #253e5c; font-weight: normal;'>" + Utility.IsNullStr(dr["Duration"]) +"</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>Medio:</b><b style='color: #253e5c; font-weight: normal;'>" + Utility.IsNullStr(dr["MediaDescription"]) + "</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>Seccion:</b><b style='color: #253e5c; font-weight: normal;'>" + Utility.IsNullStr(dr["SectionDescription"])+"</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>Calificación:</b><b style='color: #253e5c; font-weight: normal;'>" + Utility.IsNullStr(dr["Calification"]) + "</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += " <b style='color: #cd9035'>Importancia:</b><b style='color: #253e5c; font-weight: normal;'>" + Utility.IsNullStr(dr["Important"]) + "</b>&nbsp;&nbsp;&nbsp;";
                            //Mailbody += "<p style='color: #27436a; font-size: 10px;'>" + Utility.IsNullStr(dr["NewsSummary"]) + " </p>";
                            //Mailbody += " <p style='text-align: center'><a style='color: #722932;' href='http://mediamonitor.do/users/NewsAlert.aspx' target='_blank'>Read More</a></p></td></tr>";
                            
                            #endregion
                        } //end loop dt
                        ts.Complete();
                        //Mailbody+="<table>";
                        //SendMail(Mailbody);
                       
                    }//end try
                    catch (Exception ex2)
                    {
                        ts.Dispose();
                        conn.Dispose();
                    }
                }//end using SharedDBConnection
            }//end using TransactionScope
            errMsg = "<h2>Data Uploaded Successfully</h2>All data from RF file successfully uploaded";
            return true;
        }
        //private void SendMail(string message)
        //{
        //    String msg = "Estimado Usuario,<br/><br/> <br/>Hemos cargado nuevas alertas y noticias de su interés, a continuación un resumen de ellas:<br/><br/>";
        //    msg = msg + message;
        //    String title = "Alertas y noticias AGB-CDI";
        //    String toAccount = "rishab@nascenture.com";
        //    //String toAccount = "niteshguleria@nascenture.com";
        //    String MyHost = ConfigurationManager.AppSettings["SMTPHost"].ToString();
        //    String MySMTPPort = ConfigurationManager.AppSettings["SMTPPort"].ToString();
        //    String MySMTPSSL = ConfigurationManager.AppSettings["SMTPSSL"].ToString();
        //    String fromAddr = AdminEmail;   //Email Address of Sender
        //    MailMessage smtpMssg = new MailMessage();
        //    smtpMssg.From = new MailAddress(fromAddr);
        //    if (toAccount.Contains(","))
        //    {
        //        string[] tos = toAccount.Split(',');
        //        for (int i = 0; i < tos.Length; i++)
        //        {
        //            if (tos[i] == "," || tos[i] == "")
        //            {
        //            }
        //            else
        //            {
        //                smtpMssg.To.Add(new MailAddress(tos[i].Replace(",", "")));
        //            }
        //        }
        //    }
        //    else
        //    {
        //        smtpMssg.To.Add(new MailAddress(toAccount));
        //    }
        //    smtpMssg.Subject = title;
        //    smtpMssg.Body = msg;
        //    smtpMssg.IsBodyHtml = true;
        //    System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient(MyHost, Convert.ToInt32(MySMTPPort));
        //    smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
        //    if (MySMTPSSL == "true")
        //    {
        //        smtpClient.EnableSsl = true;
        //    }

        //    try
        //    {
        //        smtpClient.Send(smtpMssg);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Email not sent | Reason: [" + ex.Message + "]");
        //    }
        //}
        private System.Data.DataTable GetRFDataTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
            dt.Columns.Add("MediaCode", System.Type.GetType("System.Int32")); //1
            dt.Columns.Add("RFDate", System.Type.GetType("System.DateTime")); //2
            dt.Columns.Add("MediaDescription", System.Type.GetType("System.String")); //3
            dt.Columns.Add("SectionCode", System.Type.GetType("System.Int32")); //4
            dt.Columns.Add("SectionDescription", System.Type.GetType("System.String")); //5 
            dt.Columns.Add("PageorHour", System.Type.GetType("System.String"));//6
            dt.Columns.Add("Duration", System.Type.GetType("System.String"));//7
            dt.Columns.Add("Advertiser", System.Type.GetType("System.String"));//8
            dt.Columns.Add("Title", System.Type.GetType("System.String"));//9      Cost
            dt.Columns.Add("Calification", System.Type.GetType("System.String"));//10         
            dt.Columns.Add("Important", System.Type.GetType("System.String"));//11      
            dt.Columns.Add("NewsSummary", System.Type.GetType("System.String"));//12  
            dt.Columns.Add("OthersBrandsDnvolved", System.Type.GetType("System.String"));//13
            dt.Columns.Add("OthersPeoplesInvolved", System.Type.GetType("System.String"));//14
            dt.Columns.Add("Link", System.Type.GetType("System.String"));//15
            dt.Columns.Add("ImageAudioVideoName", System.Type.GetType("System.String"));//16
            dt.Columns.Add("News", System.Type.GetType("System.String"));//17
            dt.Columns.Add("Company", System.Type.GetType("System.String"));//18
            return dt;
        }
    }
}
